Java Object Oriented Querying

jOOQ stands for Java Object Oriented Querying. It is a very light database mapping library in Java. Its purpose is to be both relational and object oriented by providing a Domain-specific language to construct queries from classes generated from a database schema. Instead of providing object-relational mapping, it implements an Active record pattern.

Paradigm

jOOQ claims that SQL should come first in any database integration. While it provides a lot of abstraction on top of JDBC, it does not have as much functionality and complexity as Hibernate or JPA or similar products as far as OR-mapping is concerned. It is essentially a compromise between the two. SQL has many features that cannot be used in an object oriented programming paradigm. This is a major drawback of the object-relational model.

With jOOQ, no new textual query language is introduced, but plain SQL can be constructed using jOOQ objects and code generated from a schema. This prevents syntax errors and type mapping problems. Also, variable binding is taken care of. Without the OR-mapping overhead, however, it is also possible to create very complex queries, with aliasing, unions, nested selects, complex joins. Besides, jOOQ also supports database-specific features, such as UDTs, enum types, stored procedures and native functions.

Example

A nested query selecting from an aliased table

  -- Select authors with books that are sold out
  SELECT * 
    FROM T_AUTHOR a
   WHERE EXISTS (SELECT 1
                   FROM T_BOOK
                  WHERE T_BOOK.STATUS = 'SOLD OUT'
                    AND T_BOOK.AUTHOR_ID = a.ID);

And its equivalent in jOOQ DSL:

  // Alias the author table
  Table<TAuthorRecord> a = T_AUTHOR.as("a");
 
  // Use the aliased table in the select statement
  create.selectFrom(a)
        .where(create.exists(create.select(create.constant(1))
                                   .from(T_BOOK)
                                   .where(TBook.STATUS.equal(TBookStatus.SOLD_OUT)
                                   .and(TBook.AUTHOR_ID.equal(a.getField(TAuthor.ID))))));

See jOOQ Manual: DSL Support for many more examples

See also

• jOOQ Home
• Hibernate
• iBATIS
• Ebean
• QueryDSL
• JaQu
• List of object-relational mapping software